﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Threading.Tasks;
using QQTools.Data.Models;

namespace QQTools.Data.Repositories
{

    public class QQRepository
    {
        private readonly string _connectionString;

        public QQRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public async Task<List<QQModel>> GetAllAsync()
        {
            var accounts = new List<QQModel>();
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM qq", connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            accounts.Add(MapTo(reader));
                        }
                    }
                }
            }
            return accounts;
        }
        public async Task<QQModel> GetByIdAsync(int id)
        {
            QQModel account = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM qq WHERE id = @Id", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            return MapTo(reader);
                        }
                    }

                }
            }

            return account;
        }

        public async Task AddAsync(QQModel account)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    INSERT INTO qq (qq, nickname, title, last_handle, last_pid, group_number)
                    VALUES (@QQ, @Nickname, @Title, @LastHandle, @LastPid, @GroupNumber);", connection))
                {
                    command.Parameters.AddWithValue("@QQ", account.QQ);
                    command.Parameters.AddWithValue("@Nickname", account.Nickname);
                    command.Parameters.AddWithValue("@Title", account.Title);
                    command.Parameters.AddWithValue("@LastHandle", account.LastHandle);
                    command.Parameters.AddWithValue("@LastPid", account.LastPid);
                    command.Parameters.AddWithValue("@GroupNumber", account.GroupNumber);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task UpdateAsync(QQModel account)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE qq
                    SET qq = @QQ, nickname = @Nickname, title = @Title, last_handle = @LastHandle, last_pid = @LastPid, group_number = @GroupNumber, update_time = CURRENT_TIMESTAMP
                    WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", account.Id);
                    command.Parameters.AddWithValue("@QQ", account.QQ);
                    command.Parameters.AddWithValue("@Nickname", account.Nickname);
                    command.Parameters.AddWithValue("@Title", account.Title);
                    command.Parameters.AddWithValue("@LastHandle", account.LastHandle);
                    command.Parameters.AddWithValue("@LastPid", account.LastPid);
                    command.Parameters.AddWithValue("@GroupNumber", account.GroupNumber);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task DeleteAsync(string qqNumber)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("DELETE FROM qq WHERE qq = @QQ;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qqNumber);
                    await command.ExecuteNonQueryAsync();
                }
            }
        }
        public async Task AddOrUpdateAsync(QQModel account)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();

                // 检查 qq 是否已经存在
                using (var checkCommand = new SQLiteCommand("SELECT id FROM qq WHERE qq = @QQ", connection))
                {
                    checkCommand.Parameters.AddWithValue("@QQ", account.QQ);
                    var existingId = await checkCommand.ExecuteScalarAsync();
                    if (existingId != null)
                    {
                        // 如果存在，则更新数据
                        int id = Convert.ToInt32(existingId);
                        account.Id = id;
                        await UpdateAsync(account);
                    }
                    else
                    {
                        // 如果不存在，则插入新数据
                        await AddAsync(account);
                    }
                }
            }
        }
        public async Task<QQModel> GetByQQAsync(string qq)
        {
            QQModel qqInfo = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM qq WHERE qq = @QQ", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            return MapTo(reader);
                        }
                    }
                }
            }
            return qqInfo;
        }



        /// <summary>
        /// 映射 SqlDataReader 到 QQEntities 对象
        /// </summary>
        public QQModel MapTo(System.Data.Common.DbDataReader reader)
        {
            return new QQModel
            {
                Id = reader.GetInt32(reader.GetOrdinal("id")),
                QQ = reader.GetString(reader.GetOrdinal("qq")),
                Nickname = reader["nickname"] as string,
                Title = reader["title"] as string,
                LastHandle = new IntPtr(reader.GetInt32(reader.GetOrdinal("last_handle"))),
                LastPid = (int)reader.GetInt32(reader.GetOrdinal("last_pid")),
                GroupNumber = reader["group_number"] as string,
                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
            };
        }
    }
}
